package com.library.dao;

import com.library.model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class UserDAO extends BaseDAO {

    /**
     * 根据账号密码查询用户
     */
    public User getUserByUsernameAndPassword(String username, String password) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        ResultSet rs = null;
        User user = null;

        try {
            conn = getConnection(); // 使用BaseDAO的连接方法
            String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setRole(rs.getString("role"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, rs); // 使用BaseDAO的关闭方法
        }
        return user;
    }

    /**
     * 多条件查询用户
     */
    public List<User> findUserByCondition(String username, String email, String phone, String role) {
        List<User> users = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        StringBuilder sql = new StringBuilder("SELECT * FROM user WHERE 1=1");
        List<String> params = new ArrayList<>();

        if (username != null && !username.trim().isEmpty()) {
            sql.append(" AND username LIKE ?");
            params.add("%" + username + "%");
        }
        if (email != null && !email.trim().isEmpty()) {
            sql.append(" AND email LIKE ?");
            params.add("%" + email + "%");
        }
        if (phone != null && !phone.trim().isEmpty()) {
            sql.append(" AND phone LIKE ?");
            params.add("%" + phone + "%");
        }
        if (role != null && !role.trim().isEmpty()) {
            sql.append(" AND role LIKE ?");
            params.add("%" + role + "%");
        }

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setString(i + 1, params.get(i));
            }
            rs = pstmt.executeQuery();

            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setRole(rs.getString("role"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, rs);
        }
        return users;
    }

    /**
     * 根据id删除
     */
    public int delete(int id) {
        String sql = "DELETE FROM user WHERE id=?";
        try {
            return executeUpdate(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 添加用户
     */
    public int add(User user) {
        String sql = "INSERT INTO user (username, password, email, phone, role, created_at) VALUES (?, ?, ?, ?, ?, ?)";
        try {
            return executeUpdate(sql,
                    user.getUsername(),
                    user.getPassword(),
                    user.getEmail(),
                    user.getPhone(),
                    user.getRole(),
                    LocalDateTime.now()
            );
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改用户
     */
    public int update(User user) throws SQLException {
        // 校验必要参数
        if (user.getId() == null) {
            throw new IllegalArgumentException("更新用户时，ID 不能为空");
        }

        // 动态拼接 SQL
        StringBuilder sql = new StringBuilder("UPDATE user SET ");
        List<Object> params = new ArrayList<>();

        // 添加username
        sql.append("username = ?");
        params.add(user.getUsername());

        // 处理password（不为空则添加）
        String password = user.getPassword();
        if (password != null && !password.trim().isEmpty()) {
            sql.append(", password = ?");
            params.add(password.trim());
        }

        // 添加后续字段
        sql.append(", email = ?, phone = ?, role = ? ");
        params.add(user.getEmail());
        params.add(user.getPhone());
        params.add(user.getRole());

        // 拼接WHERE条件
        sql.append("WHERE id = ?");
        params.add(user.getId());

        return executeUpdate(sql.toString(), params.toArray());
    }
}
